/**
 * 
 */
package com.rnit.jexcel.neg;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.rnit.jexcel.bean.ModeloBean;

/**
 * @author rmsilva
 *
 */
public class CargaModelo {

	/**
	 * 
	 */
	public CargaModelo() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		try 
		{ 
			
			System.out.println(" Lendo planilha . . .");
			
			Workbook workbook = Workbook.getWorkbook(new File("/workspace/JExcel/src/com/rnit/jexcel/neg/tabelamolicar.xls"));
			//Workbook workbook = Workbook.getWorkbook(new File("/eclipseIndigo/workspace/JExcel/src/com/rnit/jexcel/neg/tabelamolicar.xls")); 
			Sheet sheet = workbook.getSheet(0);
			
		//	ModeloBean ModeloCarregado = null;
			
			for (int i=1; i<sheet.getRows(); i++) {
				
				Cell codigoTipoVeiculo = sheet.getCell(0,i);
				Cell codigoMarca       = sheet.getCell(2,i);
				Cell descricaoTipo     = sheet.getCell(4,i);
				Cell descricaoVersao   = sheet.getCell(5,i);
				
				if( codigoMarca.getContents() != null && codigoTipoVeiculo.getContents() != null ) {
					if(!codigoMarca.getContents().equals("") && !codigoTipoVeiculo.getContents().equals("")){
				
//						if(ModeloCarregado == null || (!ModeloCarregado.getIdMarca().equals(codigoMarca.getContents()) 
//								|| !ModeloCarregado.getIdTipo().equals(codigoTipoVeiculo.getContents()))) {
//					
							
							ModeloBean modelo = new ModeloBean();
							
							modelo.setIdMarca(codigoMarca.getContents());
							modelo.setIdTipo(codigoTipoVeiculo.getContents());
							modelo.setDescricaoModelo(descricaoTipo.getContents() + " " + descricaoVersao.getContents());
								
							System.out.println(" gravando ...");
							System.out.println("codigoMarca.getContents(): " + codigoMarca.getContents());
							System.out.println("codigoTipoVeiculo.getContents(): " + codigoTipoVeiculo.getContents());
							System.out.println("Modelo: " + descricaoTipo.getContents() + " " + descricaoVersao.getContents());
							
							InsertModelo(modelo);
							
//							ModeloCarregado = new ModeloBean();
//							ModeloCarregado.setIdMarca(modelo.getIdMarca());
//							ModeloCarregado.setIdTipo(modelo.getIdTipo());
//							ModeloCarregado.setDescricaoModelo(modelo.getDescricaoModelo());
							
								
						}
					//}
				}
			}
			
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void InsertModelo (ModeloBean modelo) {
		
		
		Connection connection = null; 
		
		try {
			
			
			// Load the JDBC driver 
			String driverName = "com.mysql.jdbc.Driver"; // MySQL JDBC driver 
			Class.forName(driverName); 
		
			// Create a connection to the database 
			String serverName = "localhost:3306"; 
			String mydatabase = "cnpv?autoReconnect=true"; 
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url 
			
			String username = "root"; 
			String password = "admin"; 
			
			connection = DriverManager.getConnection(url, username, password); 
			
			StringBuilder q = new StringBuilder();
			
			q.append("insert into modelo (");
			q.append(" id_marca, ");
			q.append(" ds_modelo, ");
			q.append(" id_tipo_veiculo, ");
			q.append(" dt_incl, ");
			q.append(" id_usuario ");
			q.append(" )");
			q.append("values (?,?,?,?,1)");
			
			PreparedStatement ps = connection.prepareStatement(q.toString());

			ps.setString(1, modelo.getIdMarca()); 
			ps.setString(2, modelo.getDescricaoModelo()); 
			ps.setString(3, modelo.getIdTipo());
			ps.setTimestamp(4, new Timestamp(new java.util.Date().getTime()));
			
			ps.executeUpdate();
			ps.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
	}

}
